package fina2.xls;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Properties;
import java.util.TreeMap;

import javax.xml.bind.JAXBContext;
import javax.xml.bind.Marshaller;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import fina2.dcs.common.returns.jaxb.HEADERTYPE;
import fina2.dcs.common.returns.jaxb.ITEMTYPE;
import fina2.returns.cbn.jaxb.AuthSignatoryType;
import fina2.returns.cbn.jaxb.BodyType;
import fina2.returns.cbn.jaxb.CallReport;
import fina2.returns.cbn.jaxb.ContactDetailsType;
import fina2.returns.cbn.jaxb.FooterType;
import fina2.returns.cbn.jaxb.HeaderType;
import fina2.returns.cbn.jaxb.ItemsInfoType;


/**
 * 
 * @author datojava Instead of fina2.xls.XLSReader please use
 *         fina2.dcs.common.xls.XLSReader
 */
@SuppressWarnings("unused")
@Deprecated
public class XLSReader {

	private File matrix;
	private File xls;
	private InputStream matrixIn;
	private InputStream xlsIn;
	private POIFSFileSystem matrixPoi;
	private POIFSFileSystem xlsPoi;
	private HSSFWorkbook matrixWb;
	private HSSFWorkbook xlsWb;
	private Properties p = null;
	private Logger log = Logger.getLogger(XLSReader.class);

	/**
	 * Initializes matrix workbook and xls workbook
	 * 
	 * @param matrix
	 * @param xls
	 */
	public XLSReader(File matrix, File xls) {
		this.matrix = matrix;
		this.xls = xls;
		try {
			matrixIn = new FileInputStream(matrix);
			xlsIn = new FileInputStream(xls);
			matrixPoi = new POIFSFileSystem(matrixIn);
			xlsPoi = new POIFSFileSystem(xlsIn);
			matrixWb = new HSSFWorkbook(matrixPoi);
			xlsWb = new HSSFWorkbook(xlsPoi);

			p = new Properties();
			p.load(this.getClass().getResourceAsStream("/matrixsymbols.properties"));
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
	}

	public XLSReader(File matrix) {
		this.matrix = matrix;
		try {
			matrixIn = new FileInputStream(matrix);
			matrixPoi = new POIFSFileSystem(matrixIn);
			matrixWb = new HSSFWorkbook(matrixPoi);
			p = new Properties();
			p.load(this.getClass().getResourceAsStream("/matrixsymbols.properties"));
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}

	}

	private int[] getXYReferenceFromMatrix(String sheetName, int rowIndex, int colIndex) {
		int rowCol[] = new int[2];
		Object value = null;

		HSSFSheet sheet = matrixWb.getSheet(sheetName);
		HSSFRow row = sheet.getRow(rowIndex);
		if (row != null) {
			HSSFCell cell = row.getCell(colIndex);
			if (cell != null) {
				if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)
					value = cell.getCellComment();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
					value = cell.getBooleanCellValue();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_ERROR)
					value = cell.getErrorCellValue();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
					value = cell.getCellFormula();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
					value = cell.getNumericCellValue();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
					value = cell.getRichStringCellValue();
			}
		}

		if (value != null) {
			try {
				rowCol[0] = Integer.parseInt(value.toString().trim().substring(1)) - 1;
				rowCol[1] = Integer.parseInt(p.get(value.toString().trim().substring(0, 1)).toString());
			} catch (Exception ex) {
				log.error(ex.getMessage(), ex);
				ex.printStackTrace();
			}
		}
		return rowCol;
	}

	public Object getValueByRowCol(HSSFWorkbook wb, String sheetName, int rowIndex, int colIndex) {
		Object value = null;
		if (wb == null)
			return value;
		HSSFSheet sheet = wb.getSheet(sheetName);
		HSSFRow row = sheet.getRow(rowIndex);
		if (row != null) {
			HSSFCell cell = row.getCell(colIndex);
			if (cell != null) {
				if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)
					value = cell.getCellComment();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
					value = cell.getBooleanCellValue();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_ERROR)
					value = cell.getErrorCellValue();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
					value = cell.getCellFormula();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
					value = cell.getNumericCellValue();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
					value = cell.getRichStringCellValue();
			}
		}
		return value;
	}

	private boolean isNumber(String s) {
		boolean isNum = true;
		try {
			Long.parseLong(s);
		} catch (NumberFormatException ex) {
			isNum = false;
		}
		return isNum;
	}

	/**
	 * 
	 * @param sheetName
	 * @return the HeaderType object which contains the header information of
	 *         generated xml.
	 */
	public HEADERTYPE getHeaderType(String sheetName) {
		log.info("Generating xml header...");
		HEADERTYPE ht = new HEADERTYPE();
		String s = xls.getName();

		String returnCode = null;

		for (int i = 1;; i++) {
			Object o = getValueByRowCol(matrixWb, "Options", i, 12);
			String str = null;
			if (o != null) {
				str = o.toString();
				if (sheetName.equals(str)) {
					returnCode = getValueByRowCol(matrixWb, "Options", i, 1).toString();
					break;
				}
			} else {
				break;
			}
		}

		if (s.charAt(3) != '.') {

			ht.setRETURNCODE(returnCode);
			ht.setBANKCODE(s.substring(3, 8));
			String month = s.substring(9, 11);
			String year = s.substring(11, 15);
			ht.setPERIODFROM("01/" + month + "/" + year);
			Integer m = new Integer(month);
			if (m != 2) {
				if (m == 8) {
					ht.setPERIODEND("31/" + month + "/" + year);
				} else if (m < 8) {
					if (m % 2 == 0) {
						ht.setPERIODEND("30/" + month + "/" + year);
					} else {
						ht.setPERIODEND("31/" + month + "/" + year);
					}
				} else {
					if (m % 2 == 0) {
						ht.setPERIODEND("31/" + month + "/" + year);
					} else {
						ht.setPERIODEND("30/" + month + "/" + year);
					}
				}
			} else {
				Integer y = new Integer(year);
				String ss = (y % 4 == 0) ? "29" : "28";
				ht.setPERIODEND(ss + "/" + month + "/" + year);
			}
		} else {
			ht.setRETURNCODE(returnCode);
			ht.setBANKCODE(s.substring(6, 11));
			String month = s.substring(12, 14);
			String year = s.substring(14, 18);
			ht.setPERIODFROM("01/" + month + "/" + year);
			Integer m = new Integer(month);
			if (m != 2) {
				if (m == 8) {
					ht.setPERIODEND("31/" + month + "/" + year);
				} else if (m < 8) {
					if (m % 2 == 0) {
						ht.setPERIODEND("30/" + month + "/" + year);
					} else {
						ht.setPERIODEND("31/" + month + "/" + year);
					}
				} else {
					if (m % 2 == 0) {
						ht.setPERIODEND("31/" + month + "/" + year);
					} else {
						ht.setPERIODEND("30/" + month + "/" + year);
					}
				}
			} else {
				Integer y = new Integer(year);
				String ss = (y % 4 == 0) ? "29" : "28";
				ht.setPERIODEND(ss + "/" + month + "/" + year);
			}
		}
		Object bankName = "Bank Name";// getValueByRowCol(xlsWb, sheetName, 1,
		// 2);
		Object retName = "Return Name";// getValueByRowCol(xlsWb, sheetName, 3,
		// 2);
		ht.setBANKNAME((bankName != null) ? bankName.toString() : " ");
		ht.setRETURNNAME((retName != null) ? retName.toString() : " ");
		ht.setVER("ORI");
		ht.setSIGNED("null");
		ht.setLNG("en_US");
		log.info("XML header generated....");
		return ht;
	}

	private boolean rowEmpty(String sheetName, int colStartIndex, int colEndIndex, int rowIndex) {
		boolean empty = true;
		int emptyCounter = 0;
		for (int i = colStartIndex; i <= colEndIndex; i++) {
			Object val = getValueByRowCol(xlsWb, sheetName, rowIndex, i);
			if (val != null && val.toString().trim().length() != 0) {
				empty = false;
				break;
			}
		}
		return empty;
	}

	private boolean isVct(String sheetName) {
		boolean vct = true;
		Object val = getValueByRowCol(matrixWb, sheetName, 0, 3);
		if (val == null || val.toString().trim().length() == 0)
			vct = false;
		return vct;
	}

	public int getEofIndexRow(HSSFWorkbook matrixWb, String sheetName, int vctStartRow) {
		int index = vctStartRow;
		Object code = null;
		int r = 1;

		while ((code = getValueByRowCol(matrixWb, "options", r, 1)) != null) {
			if (code.toString().toLowerCase().trim().contains(sheetName.toLowerCase().trim()))
				break;
			else
				r++;
		}

		Object eofOneColName = getValueByRowCol(matrixWb, "options", r, 8).toString();
		Object eofOneValue = getValueByRowCol(matrixWb, "options", r, 9);
		Object eofTwoColName = getValueByRowCol(matrixWb, "options", r, 10).toString();
		Object eofTwoValue = getValueByRowCol(matrixWb, "options", r, 11);
		int eofOneColIndex = Integer.parseInt(p.get(eofOneColName).toString());
		int eofTwoColIndex = Integer.parseInt(p.get(eofTwoColName).toString());

		for (int i = vctStartRow;; i++) {
			Object s1 = getValueByRowCol(xlsWb, sheetName, i, eofOneColIndex);
			Object s2 = getValueByRowCol(xlsWb, sheetName, i, eofTwoColIndex);
			if (s1 == null)
				s1 = "";
			if (s2 == null)
				s2 = "";
			if (eofOneValue == null)
				eofOneValue = "";
			if (eofTwoValue == null)
				eofTwoValue = "";
			if ((s1.toString().trim().toLowerCase().equals(eofOneValue.toString().trim().toLowerCase())) && (s2.toString().trim().toLowerCase().equals(eofTwoValue.toString().trim().toLowerCase()))) {
				index = i;
				break;
			}

		}

		return index;
	}

	/**
	 * 
	 * @param sheetName
	 * @return the List of ItemsInfoType,each ItemsInfoType object containts
	 *         code,description and ammount
	 */
	public List<ITEMTYPE> getAllItems(String sheetName) {
		log.info("Generating xml items for sheet " + sheetName);

		ArrayList<ITEMTYPE> data = new ArrayList<ITEMTYPE>();
		// ////////////////////////////////////////////

		if (!isVct(sheetName)) {
			for (int i = 1;; i++) {
				ITEMTYPE item = new ITEMTYPE();
				Object code = getValueByRowCol(matrixWb, sheetName, i, 0);
				if ((code == null) || (code.toString().trim().length() == 0))
					break;
				else {
					item.setITEMCODE(code.toString().trim());
					item.setROW(0);
					Object val = getValueByRowCol(xlsWb, sheetName, getXYReferenceFromMatrix(sheetName, i, 2)[0], getXYReferenceFromMatrix(sheetName, i, 2)[1]);
					if (val != null)
						item.setVALUE(val.toString());
					else
						item.setVALUE("");
					data.add(item);
				}
			}
		} else {

			Float vctStartRow = (Float.parseFloat(getValueByRowCol(matrixWb, sheetName, 0, 4).toString())) - 1;
			int eofIndexRow = getEofIndexRow(matrixWb, sheetName, vctStartRow.intValue());
			for (int j = 1;; j++) {
				Object code = getValueByRowCol(matrixWb, sheetName, j, 0);
				if ((code == null) || (code.toString().trim().length() == 0))
					break;
				Object colName = getValueByRowCol(matrixWb, sheetName, j, 2);
				if ((colName == null) || (colName.toString().trim().length() == 0))
					break;

				Integer colIndex = Integer.parseInt(p.get(colName.toString()).toString());

				for (int i = vctStartRow.intValue(), k = 0; i < eofIndexRow; i++, k++) {
					ITEMTYPE item = new ITEMTYPE();
					item.setITEMCODE(code.toString().trim());
					item.setROW(k);
					Object val = getValueByRowCol(xlsWb, sheetName, i, colIndex);
					item.setVALUE((val != null) ? val.toString() : "");
					data.add(item);
				}

			}
			/*
			 * int lastRowIndex = -1; int endColIndex = 0;
			 * 
			 * for (int j = 1;; j++) { Object code = getValueByRowCol(matrixWb,
			 * sheetName, j, 0); if ((code == null) ||
			 * (code.toString().trim().length() == 0)) break; else endColIndex =
			 * j + 1; } for (int j = 1;; j++) { int rowIndex = new Integer(new
			 * Float(getValueByRowCol(matrixWb, sheetName, 0,
			 * 4).toString()).intValue()) - 1; Object code =
			 * getValueByRowCol(matrixWb, sheetName, j, 0);
			 * 
			 * if ((code == null) || (code.toString().trim().length() == 0))
			 * break; String symbol = getValueByRowCol(matrixWb, sheetName, j,
			 * 2).toString(); int colIndex = new Integer(p.getProperty(symbol));
			 * 
			 * if (rowEmpty(sheetName, colIndex, endColIndex, rowIndex)) {
			 * break; }
			 * 
			 * for (int k = rowIndex;; rowIndex++) { if (rowEmpty(sheetName,
			 * colIndex, endColIndex, rowIndex)) { rowEmpty = true; break; } if
			 * (rowIndex == lastRowIndex) break; ITEMTYPE item = new ITEMTYPE();
			 * 
			 * item.setITEMCODE(code.toString().trim()); item.setROW(rowIndex -
			 * k); Object val = getValueByRowCol(xlsWb, sheetName, rowIndex,
			 * colIndex); if ((val != null) && (val.toString().trim().length()
			 * != 0) && (val.toString().toLowerCase().trim().equals("total"))) {
			 * lastRowIndex = rowIndex; break; } if ((val == null) ||
			 * (val.toString().trim().length() == 0)) { break; } else
			 * item.setVALUE(val.toString().trim()); data.add(item); } }
			 */

		}
		log.info("XML items generated...");
		return data;
	}

	/**
	 * 
	 * @return the array of matrix sheets names
	 */
	public String[] getAllSheets() {
		String sheets[] = new String[matrixWb.getNumberOfSheets()];
		for (int i = 0; i < sheets.length; i++)
			sheets[i] = matrixWb.getSheetName(i);
		return sheets;
	}

	public TreeMap<String, Boolean> loadProtectionProperties() {
		TreeMap<String, Boolean> optionsMap = new TreeMap<String, Boolean>();
		Object retName = null;
		if (matrixWb == null)
			return optionsMap;
		int j = 1;
		try {
			while ((retName = getValueByRowCol(matrixWb, "options", j, 1)) != null) {
				String key = retName.toString().trim().substring(4);
				String val = getValueByRowCol(matrixWb, "options", j, 7).toString().trim().toLowerCase();
				Boolean b;
				if (val.equals("true"))
					b = true;
				else
					b = false;
				optionsMap.put(key, b);
				j++;
			}
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
		return optionsMap;
	}

	public File getXls() {
		return xls;
	}

}